﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Model;
using System.Data;
using System.Data.SqlClient;

namespace DAL
{
    public class TableInfoDAL : DB
    {
        public TableInfoDAL(LoginInfo info)
        {
            base.loginInfo = info;
        }
        public IList<TableInfo> GetStagingTableInfo(string schemaName, string tableName)
        {
            IList<TableInfo> result = new List<TableInfo>();
            try
            {
                HIDB.dbo conn = base.conn();
                string sql = @"SELECT a.序号,a.架构,a.表名,a.列名,a.数据类型,a.数据定义,a.长度,a.精度,a.小数位,a.默认值,a.标识,b.ColumnIsPK as 主键,b.ColumnIsPK as 允许空,a.注释
FROM [dbo].[FN_SYS_GetTableInfo] (@schemaName,@tableName) a inner join Working_db.ETL_Source_View_ColumnConfig   b on
a.列名 = b.ColumnName inner join Working_db.ETL_Source_ViewConfig v on b.ViewID=v.GUID
where 列名 not in ('mdp_guid','mdp_batchtime','mdp_isdelete','mdp_operationtype') and v.MDP_ViewName=@tableName";

                DataTable dt = conn.GetDataTable(sql, new SqlParameter[]
                {
                     new SqlParameter("@schemaName",schemaName)
                    ,new SqlParameter("@tableName",tableName)
                });
                if (dt != null && dt.Rows.Count > 0)
                {
                    result = new List<TableInfo>();
                    TableInfo _temp = null;
                    foreach (DataRow item in dt.Rows)
                    {
                        _temp = new TableInfo
                        {
                            ColumnSourceID = item["表名"].ToString(),
                            TableName = item["表名"].ToString(),
                            ColumnName = item["列名"].ToString(),
                            Desc = item["注释"].ToString(),
                            FactColumnName1 = item["列名"].ToString(),
                            ColumnType = item["数据类型"].ToString(),
                            ColumnTypeSource = item["数据类型"].ToString(),
                            Length = int.Parse(item["长度"].ToString()),
                            Precision = int.Parse(item["精度"].ToString()),
                            DecimalPlaces = int.Parse(item["小数位"].ToString()),
                            PK = item["主键"].ToString(),
                            IsNull = item["主键"].ToString() == "1" ? "0" : "1",
                            DefaultValue = item["默认值"].ToString()
                        };
                        result.Add(_temp);
                    }
                }
            }
            catch (Exception)
            {

                throw;
            }
            return result;
        }
        public IList<TableInfo> GetStagingTableInfo(string viewID)
        {
            IList<TableInfo> result = new List<TableInfo>();
            try
            {
                HIDB.dbo conn = base.conn();
                string sql = @"select distinct * from [FN_SYS_GetTableInfoForSource](@ViewID) a";

                DataTable dt = conn.GetDataTable(sql, new SqlParameter[]
                {
                     new SqlParameter("@ViewID",viewID)
                });
                if (dt != null && dt.Rows.Count > 0)
                {
                    result = new List<TableInfo>();
                    TableInfo _temp = null;
                    foreach (DataRow item in dt.Rows)
                    {
                        _temp = new TableInfo
                        {
                            ColumnSourceID = item["表名"].ToString(),
                            TableName = item["表名"].ToString(),
                            ColumnName = item["列名"].ToString(),
                            Desc = item["注释"].ToString(),
                            FactColumnName1 = item["列名"].ToString(),
                            ColumnType = item["数据类型"].ToString(),
                            ColumnTypeSource = item["数据类型"].ToString(),
                            Length = int.Parse(item["长度"].ToString()),
                            Precision = int.Parse(item["精度"].ToString()),
                            DecimalPlaces = int.Parse(item["小数位"].ToString()),
                            PK = item["主键"].ToString(),
                            IsNull = item["允许空"].ToString(),
                            DefaultValue = item["默认值"].ToString()
                        };
                        result.Add(_temp);
                    }
                }
            }
            catch (Exception)
            {

                throw;
            }
            return result;
        }

        public List<string> GetTableColumns(string table)
        {
            //创建数据库对象
            HIDB.dbo con = base.conn();
            List<string> list = new List<string>();
            DataTable dt = new DataTable();
            try
            {
                IDataParameter[] paras = new SqlParameter[1];
                paras[0] = new SqlParameter("@TableName", table);
                dt = con.GetDataTable("select c.name from sys.columns c left join  sys.extended_properties p on c.object_id=p.major_id and c.column_id=p.minor_id  left join sys.types t on c.user_type_id=t.user_type_id where c.object_id=object_id(@TableName) order by c.column_id", paras);
                if (dt != null && dt.Rows.Count > 0)
                {
                    foreach (DataRow row in dt.Rows)
                    {
                        list.Add(row["name"].ToString());
                    }
                }
            }
            catch (Exception ex) { throw ex; }
            return list;
        }

        public IList<ModelViewConfig> GetModelViewInfo(string modelID, string version)
        {
            IList<ModelViewConfig> result = new List<ModelViewConfig>();
            try
            {
                string sql = @"select a.[GUID],a.[ModelTableID],a.[ColumnName],a.[ColumnDescription],a.[ColumnType],a.[ColumnIsModel],m.ColumnIsBusinessPK,a.[ColumnLength],a.[ColumnPrecision],a.[ColumnScale],cast(a.ColumnIsNullable as varchar(10)) ColumnIsNullable,a.[ColumnDefaultValue],a.[ColumnFixValue],a.[WhereCondition],a.[ColumnScriptExtend],cast(a.ColumnOrder as varchar(10)) ColumnOrder
from [Working_db].[Model_Column_ExtendConfig] a left join [Working_db].Model_ColumnConfig m on
a.ColumnScriptExtend='['+m.ColumnName+']' and m.ModelTableID=@modelID
 where a.ModelTableID=@modelID order by a.ColumnOrder";
                string msql = @"select str(m.ColumnOrderID) as GUID,1 as ColumnIsModel,m.ColumnName,ColumnType,m.ColumnOrderID,m.ColumnDescription,m.ColumnIsBusinessPK,m.ColumnLength,m.ColumnPrecision,m.ColumnScale 
from [Working_db].Model_ColumnConfig m where m.ModelTableID=@modelID";
                string vsql = @"select [GUID],[ModelExtendID] as ColumnSourceID,[ModelTableID],[ColumnName],[ColumnDescription],[ColumnType],[ColumnIsModel],ColumnIsBusinessPK,[ColumnLength],[ColumnPrecision],[ColumnScale],str(ColumnIsNullable) as ColumnIsNullable,[ColumnDefaultValue],[ColumnFixValue],[WhereCondition],[ColumnScriptExtend]
from [Working_db].[Model_Column_ExtendConfig_Record] where ModelTableID=@modelID and Version=@Version order by ColumnOrder";
                HIDB.dbo conn = base.conn();
                List<SqlParameter> para = new List<SqlParameter>();
                para.Add(new SqlParameter("@modelID", modelID));

                DataTable dt = new DataTable();
                if (string.IsNullOrEmpty(version))
                {
                    dt = conn.GetDataTable(sql, para.ToArray());
                    if (dt == null || dt.Rows.Count == 0)
                        dt = conn.GetDataTable(msql, para.ToArray());
                }
                else
                {
                    para.Add(new SqlParameter("@Version", version));
                    dt = conn.GetDataTable(vsql, para.ToArray());
                }
                result = Common.DBConvert.DataTableToList<ModelViewConfig>(dt);
            }
            catch (Exception)
            {

                throw;
            }
            return result;
        }
    }
}
